﻿<cfsilent>
	<cfflush interval="1024" />
	<cfscript>
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		
		teacherId = sessionAdvice.getAutherUserID();
		
		hashCode = event.getArg("Sign");
		taskId = event.getArg("TSK");
		taskType = event.getArg("TT");
		
		checked = false;
		
			
			if ( decrypt(hashCode, teacherId, "PBEWithMD5AndDES", "Hex") eq taskId ) {
				
				checked = true;
				
				/* 载入期末成绩提交任务 */
				sql = "	SELECT
							ttm.task_id,
							c.mark_mode
						FROM
							t_task_mark ttm
							INNER JOIN t_course c ON c.cid = ttm.cid
						WHERE
							ttm.task_id = :taskId ";

				queryObj = new Query(datasource = application.dnsSlave);
				
				queryObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
				
				rs_task = queryObj.execute(sql=sql).getResult();

				/* 读取成绩提交模式 */
				markMode = rs_task.mark_mode;
				
				/* 成绩提交默认为 百分制 */
				markSubmitEvent = "taskMarkWizardMode0";
		
				/* 设置为两级制 */
				if ( markMode eq "2" ) {
					markSubmitEvent = "taskMarkWizardMode2";
				}
		
				/* 设置为四级制 */
				if ( markMode eq "4" ) {
					markSubmitEvent = "taskMarkWizardMode4";
				}
				
				
				/* 导入成绩教师 */
				sql = "	SELECT
						  	t.tch_id,
						  	t.tch_name
						FROM
							t_teacher t
						WHERE
							t.tch_id = :teacherId ";

				queryObj = new Query(datasource = application.dnsSlave);
				
				queryObj.addParam(name="teacherId", value=teacherId, cfsqltype="cf_sql_vartype");
				
				rs_teacher = queryObj.execute(sql=sql).getResult();
				
				temFile = GetTempDirectory() & createUUID() & ".xls";

				markUpdate = 0;
		
				targetArgs = structNew();
				structInsert(targetArgs, "TabID", "labSheet", true);
				structInsert( targetArgs, "TSK", taskId, true );
				structInsert( targetArgs, "TT", taskType, true );
				structInsert( targetArgs, "Sign", encrypt(taskId, teacherId, "PBEWithMD5AndDES", "Hex"), true );
				
			}
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入成绩数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>
	
	<cfif isDefined("FORM.upload") and checked >
	
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="成绩名单">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入成绩信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>
				
				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "姓名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "考核成绩")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学号,姓名,考核成绩</p>
				</cfif>
				
				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p>

					<cftransaction>

						<cfloop query="rs">
							
							<cfset mark = rs["考核成绩"][rs.currentRow] />
							<cfset markNote = "1" />
							
							<cfset scroll = "" />
                            <cfset sql = "	SELECT
												stu.stu_id,
												stu.stu_name
											FROM
												t_student stu
											WHERE 
												stu.stu_id = :studentId " />
                                                
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
							
                            <cfset queryObj.addParam(name="studentId", value=rs["学号"][rs.currentRow], cfsqltype="cf_sql_varchar")/>
                            <cfset rs_student = queryObj.execute(sql=sql).getResult()/>

							<cfif rs_student.recordCount GT 0 and rs["姓名"][rs.currentRow] eq rs_student.stu_name >
								
									<!--- 学号姓名有匹配记录, 开始转置成绩信息 --->
							
									<!--- /* 根据成绩备注矫正分数 */ --->
									
									<!--- 若 缺考 --->
									<cfif mark eq "缺考">
										<cfset scroll = 0 />
										<cfset markNote = "2" />
									</cfif>
									<!--- 若 违纪 --->
									<cfif mark eq "违纪">
										<cfset scroll = 0 />
										<cfset markNote = "9" />
									</cfif>
									<!--- 若 缓考 --->
									<cfif mark eq "缓考">
										<cfset scroll = -1 />
										<cfset markNote = "3" />
									</cfif>

									<!--- 若 0-100 数字 --->
									
									<!--- /* 根据成绩计分模式调整分数 */ --->
									<cfswitch expression="#markMode#">
										
										<cfcase value="0">
											<!--- /* 百分制最多保留2位小数 */ --->
											<cfif isNumeric( mark ) and mark gte 0 and mark lte 100 >
												<cfset scroll = round( mark * 10 ) / 10 />
											</cfif>
										</cfcase>
										
										<cfcase value="2">
											<!--- /* 二级制则转换 通过 未通过 */ --->
											<cfif mark eq "通过"><cfset scroll = 90 /></cfif>
											<cfif mark eq "未通过"><cfset scroll = 0 /></cfif>
										</cfcase>
										
										<cfcase value="4">
											<!--- /* 四级制则转换 优秀 良好 合格 不合格 */ --->
											<cfif mark eq "优秀"><cfset scroll = 90 /></cfif>
											<cfif mark eq "良好"><cfset scroll = 80 /></cfif>
											<cfif mark eq "合格"><cfset scroll = 70 /></cfif>
											<cfif mark eq "不合格"><cfset scroll = 0 /></cfif>
										</cfcase>
										
									</cfswitch>
									
									<cfif isNumeric( scroll )>
									
                                            <cfset sql = "	SELECT
																ttm.scc_id
															FROM
																t_temp_mark ttm
															WHERE
																ttm.task_id = :taskId 
															AND
																ttm.stu_id = :studentId "/>

											<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                                            <cfset queryObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar")/>
                                            <cfset queryObj.addParam(name="studentId", value=rs_student.stu_id, cfsqltype="cf_sql_varchar")/>
                                            <cfset rs_mark = queryObj.execute(sql=sql).getResult()/>
											
											<cfif rs_mark.recordCount GT 0 >
											
                                                <cfset sql = "	UPDATE t_temp_mark t
															   		SET 
																   		t.mark_mode = :markMode ,
																   		t.mark_note = :markNote ,
																   		t.mark = :mark ,
																   		t.tch_id = :teacherId 
																	WHERE
																 		t.scc_id = :chooserId "/>
																		
												<cfset updateObj = new Query(datasource = application.dnsMaster)/>
												
                                                <cfset updateObj.addParam(name="markMode", value=markMode, cfsqltype="cf_sql_char")/>
                                                <cfset updateObj.addParam(name="markNote", value=markNote, cfsqltype="cf_sql_char")/>
                                                <cfset updateObj.addParam(name="mark", value=scroll, cfsqltype="cf_sql_decimal", scale=2)/>
                                                <cfset updateObj.addParam(name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar")/>
                                                <cfset updateObj.addParam(name="chooserId", value=rs_mark.scc_id, cfsqltype="cf_sql_varchar")/>
                                                <cfset updateObj.execute(sql=sql)/>
                                                
													<cfset markUpdate++ />
													
												<cfelse>
													<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 未查到成绩提交记录</p>
											</cfif>
									
										<cfelse>
											<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 考核成绩 <cfoutput>#rs["考核成绩"][rs.currentRow]#</cfoutput> 无法识别</p>
									</cfif>
								
								<cfelse>
									<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 未查到成绩提交记录</p>
							</cfif>
							
						</cfloop>

					</cftransaction>

				</cfif>
				
				
			</cfif>
		</cfif> 
		
		<p>导入成绩数据 [<cfoutput>#markUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL( markSubmitEvent, targetArgs )#</cfoutput>">刷新成绩数据</a>
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>